
package bigo.todolist.avocado;

import android.content.ContentValues;
import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * A java class representing a database the hold the tasks of the user
 * @authorAlex Irlik, Steve Fox, TiOluwa Olarewaju, Derek Miller
 * Big-Ovacado Task Force
 * Version 1.0
 *
 */

public class DBTaskAdapter 
{ 
    public static final String KEY_ROWID = "_id";
    public static final String KEY_USERNAME = "userName";
    public static final String KEY_TASKNAME= "taskName";
    public static final String KEY_DATE = "date"; 
    public static final String KEY_DESCRIPTION = "description";
    public static final String KEY_CATEGORY = "category";
    public static final String KEY_TIME = "time";
    public static final String KEY_CHECKED = "checked";
    public static final String KEY_LONGITUDE = "longitude";
    public static final String KEY_LATITUDE = "latitude";
    private static final String TAG = "DBAdapter";
    
    private static final String DATABASE_NAME = "tasksdb";
    private static final String DATABASE_TABLE = "tasks";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table tasks (_id integer primary key autoincrement, "
    	+ "userName text not null, "	
        + "taskName text not null, " 
        + "description text not null, " 
        + "date text not null, "
        + "category text not null, "
    	+ "time text not null, "
    	+ "longitude real not null, "
    	+ "latitude real not null, "
    	+ "checked text not null);";
        
    private Context context = null;  
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    /**
     * Database Adaptor constructor
     * @param ctx
     */
    public DBTaskAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
    
    /** 
     * Database helper class
     * @author Big Ohvocodo Task Force
     */  
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        /**
         * Create database if necessary
         */
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
        }

        /**
         * A means to upgrade the database
         */
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                    + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS tasks");
            onCreate(db);
        }
    }    
    
    /**
     * Open database
     * @throws SQLException
     */
    public void open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
    }
    
    /**
     * Close database file
     */
      
    public void close() 
    {
        DBHelper.close();
    }    
    
    /**
     * A method to add the task to the database
     * @param userName
     * @param taskName
     * @param date
     * @param description
     * @param category
     * @param time
     * @param longitude
     * @param latitude
     * @param checked
     * @return
     */
    public long AddTask(String userName, String taskName, String date, String description, String category, String time, double longitude, double latitude, String checked)
    {
    	Log.w("TASKMASTER","Begin db adapter 1");
    	 ContentValues initialValues = new ContentValues();
    	 initialValues.put(KEY_USERNAME, userName);
         initialValues.put(KEY_TASKNAME, taskName);
         initialValues.put(KEY_DATE, date);
         initialValues.put(KEY_DESCRIPTION, description);
         initialValues.put(KEY_CATEGORY, category);
         initialValues.put(KEY_TIME, time);
         initialValues.put(KEY_LONGITUDE, longitude);
         initialValues.put(KEY_LATITUDE, latitude);
         initialValues.put(KEY_CHECKED, checked);
         return db.insert(DATABASE_TABLE, null, initialValues);

    }
    
    /**
     * A method to retrieve all tasks of a certain category
     * @param username
     * @param category
     * @return
     */
    public String[] getTask(String username, String category)
    {
    	Cursor c;
    	ArrayList<String> taskGrab = new ArrayList<String>(); //Array list to hold the tasks
    	if(category.compareTo("All") == 0) //Retrieve all tasks regardless of category
    	{
    		c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=?", new String[]{username}); //Query all tasks
    	}
    	else //Query with category restriction
    	{
    		c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=? AND category=?", new String[]{username, category}); //Query with category restriction
    	}
    	int iName = c.getColumnIndex(KEY_DESCRIPTION); //Get column of task name
    	if(c.getCount() > 0) //Loop through and add all task names
    	{
	    	for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
	    	{
	    		if(c.getString(iName) != null)
	    			taskGrab.add(c.getString(iName)); //Add task names to array list
	    	}
    	}
    	//taskGrab.add("TEST");
    	taskGrab.trimToSize(); //Transform array list into a suitable array
    	String[] result = new String[taskGrab.size()];
		result = taskGrab.toArray(result);
    	return result;
    }
    
    /**
     * Retrieve all task descriptions
     * @param username
     * @return List of descriptions
     */
    public String[] getDescription(String username)
    {
    	Cursor c;
    	ArrayList<String> taskGrab = new ArrayList<String>(); //Array list to hold the descriptions
    	
    	c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=?", new String[]{username}); //Select all descriptions of every task
    	
    	int iDescription = c.getColumnIndex(KEY_TASKNAME);
    	
    	if(c.getCount() > 0)
    	{
	    	for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) //Loop through and add each task to the list
	    	{
	    		if(c.getString(iDescription) != null)
	    			taskGrab.add(c.getString(iDescription));
	    	}
    	}
    	//taskGrab.add("TEST");
    	taskGrab.trimToSize(); //Transform array list into a suitable array
    	String[] result = new String[taskGrab.size()];
		result = taskGrab.toArray(result);
    	return result;
    }
    
    /**
     * Alternate task retrieval method with a date restriction
     * @param username
     * @param category
     * @param date
     * @return String[] Tasks with proper dates
     */
    public String[] getTask(String username, String category, String date)
    {
    	Cursor c;
    	ArrayList<String> taskGrab = new ArrayList<String>(); //Array list to hold the tasks
    	
    	if(category.compareTo("All") == 0) //Query without category restriction
    	{
    		c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=? AND date>=?", new String[]{username,date});
    	}
    	else //Query with category restriction
    	{
    		c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=? AND category=? AND date>=?", new String[]{username,category,date});
    	}
    	
    	int iName = c.getColumnIndex(KEY_DESCRIPTION);
    	if(c.getCount() > 0)
    	{
	    	for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
	    	{
	    		if(c.getString(iName) != null)
	    			taskGrab.add(c.getString(iName)); //Loop through and add all tasks of proper date
	    	}
    	}
    	//taskGrab.add("TEST");
    	taskGrab.trimToSize(); //Transform array list into a suitable array
    	String[] result = new String[taskGrab.size()];
		result = taskGrab.toArray(result);
    	return result;
    }
    
    /**
     * 
     * @param username
     * @param category
     * @param checks
     * @return
     */
    public String[] getTask(String username, String category, ArrayList<String> checks)
    {
    	Cursor c;
    	ArrayList<String> taskGrab = new ArrayList<String>(); //Array list to hold the tasks
    	
    	if(category.compareTo("Completed") == 0) //Query completed tasks
    	{
        	checks.trimToSize();
        	String[] result = new String[checks.size()]; //Simply return the list of tasks that are known to be already checked
    		result = checks.toArray(result); //We know this through this checks arraylist
        	return result;
    	}
    	else //Query uncompleted tasks
    	{
    		c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=?", new String[]{username});
    	}
    	int iName = c.getColumnIndex(KEY_DESCRIPTION);
    	if(c.getCount() > 0)
    	{
	    	for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
	    	{
	    		if(c.getString(iName) != null)
	    			taskGrab.add(c.getString(iName)); //Add all uncompleted tasks
	    	}
    	}
    	for(String task : checks)
    	{
    		taskGrab.remove(task);
    	}
    	//taskGrab.add("TEST");
    	taskGrab.trimToSize(); //Transform array list into a suitable array
    	String[] result = new String[taskGrab.size()];
		result = taskGrab.toArray(result);
    	return result;
    }
    
    /**
     * A method query still in the works. Supposed to store completed tasks in database, but db does not accept query
     * @param username
     * @param task
     * @param checked
     */
    public void setChecked(String username, String task, String checked)
    {
    	Cursor c;
    	ContentValues values = new ContentValues();
    	values.put(KEY_CHECKED, checked);
    	c = db.rawQuery("UPDATE " + DATABASE_TABLE + " SET checked=? WHERE username=? AND taskname=?", new String[]{checked,username,task});
    	Log.w("TASKMASTER","Check!!!");
    	c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=? and checked=?", new String[]{username,"true"});
    }
    
    /**
     * Retrieves coordinates of all tasks
     * @param username
     * @return double[] coordinates
     */
    public double[] getCoordinates(String username)
    {
    	Cursor c;
    	ArrayList<Double> coordinates = new ArrayList<Double>();
    	
    	c = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE username=?", new String[]{username}); //Retrieve everything of each username
    	int iLongitude = c.getColumnIndex(KEY_LONGITUDE); //Index of latitude column
    	int iLatitude = c.getColumnIndex(KEY_LATITUDE); //Index of longitude column
    	
    	if(c.getCount() > 0)
    	{
	    	for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) //Place coordinates into a list
	    	{
	    		if(c.getString(iLongitude) != null)
	    			coordinates.add(c.getDouble(iLongitude));
	    		 if(c.getString(iLatitude) != null)
		    			coordinates.add(c.getDouble(iLatitude));
	    	}
    	}
    	
    	coordinates.trimToSize();
    	double[] result = new double[coordinates.size()];
		int index = 0; //Transform list into a suitable array
		for(Double coor : coordinates)
    	{
    		result[index] = coor.doubleValue();
    		index++;
    	}
    	return result;
    }
    
}
